﻿Create function BusinessDays (@dtStartDate datetime, @dtEndDate datetime,
@indDaysInWeek int = 5
)RETURNS int
as
begin
    declare 
    @intWeeks int
    ,@indDays int 
    ,@intSdays int
    ,@intEdays int
  
    -- Find the number of weeks between the dates. Subtract 1 
    -- since we do not want to count the current week.
  
    select @intWeeks = datediff( week, @dtStartDate, @dtEndDate) - 1
  
    -- calculate the number of days in these compelete weeks.
    select @indDays = @intWeeks * @indDaysInWeek
    
    -- Get the number of days in the starting week. 
    if @indDaysInWeek = 5

        -- If Saturday, Sunday is holiday
        if datepart( dw, @dtStartDate) = 7
            select @intSdays = 7 - datepart( dw, @dtStartDate)
        else
            select @intSdays = 7 - datepart( dw, @dtStartDate) - 1
    else
        -- If Sunday is only <st1:place>Holiday</st1:place>
        select @intSdays = 7 - datepart( dw, @dtStartDate) 
    -- Calculate the days in the last week. 
    if @indDaysInWeek = 5
        if datepart( dw, @dtEndDate) = 7
            select @intEdays = datepart( dw, @dtEndDate) - 2
        else
            select @intEdays = datepart( dw, @dtEndDate) - 1
    else
        select @intEdays = datepart( dw, @dtEndDate) - 1
    -- Sum everything together.
    RETURN @indDays + @intSdays + @intEdays
end